package com.oyjd.ybx.business.goods.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import com.ibm.icu.text.SimpleDateFormat;
import com.oyjd.ybx.bean.po.AgeType;
import com.oyjd.ybx.bean.po.Classify;
import com.oyjd.ybx.bean.po.GoodsInfo;
import com.oyjd.ybx.business.goods.vo.GoodsInfoExVO;
import com.oyjd.ybx.fw.action.Page;
import com.oyjd.ybx.fw.dao.BaseDao;

/**
 * 
 * 商品信息数据处理层
 * 
 * @author <b>欧建东<br/>
 *         Email:oyjd@163.com</b>
 * @version 1.0
 */
@Component
public class GoodsDao extends BaseDao {
	/**
	 * 获取商品信息列表数据
	 * 
	 * @param page
	 *            页面对象
	 * @param user
	 *            当前用户
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<GoodsInfoExVO> getList(Page<GoodsInfoExVO> page, String user) {
		String hqlResult = "select new " + GoodsInfoExVO.class.getName() + GoodsInfoExVO.STRUCT;
		String hqlCount = "select count(goodsinfo)";
		StringBuffer sb = new StringBuffer();
		sb.append(" from ").append(GoodsInfo.class.getName()).append(" goodsinfo,");
		sb.append(Classify.class.getName()).append(" classify,");
		sb.append(AgeType.class.getName()).append(" agetype");
		sb.append(" where goodsinfo.ageId=agetype.ageId and goodsinfo.classId=classify.classId");
		sb.append(" and ((goodsinfo.status=0 and goodsinfo.creator=?) or goodsinfo.status=1 or goodsinfo.status=2 or goodsinfo.status=3 or goodsinfo.status=4)");
		Map<String, String[]> paras = page.getParas();
		List<Object> ps = new ArrayList<Object>();
		ps.add(user);
		for (String key : paras.keySet()) {
			if ("goodsName".equals(key) || "recommend".equals(key)) {
				sb.append(" and goodsinfo.").append(key).append(" like ?");
				ps.add("%" + paras.get(key)[0] + "%");
			} else if ("ageId".equals(key)) {
				sb.append(" and goodsinfo.").append(key).append(" = ?");
				ps.add(paras.get(key)[0]);
			} else if ("status".equals(key)) {
				sb.append(" and goodsinfo.").append(key).append(" = ?");
				ps.add(Integer.parseInt(paras.get(key)[0]));
			} else if ("classId".equals(key)) {
				String v = paras.get(key)[0];
				List<Classify> list = getSonClass((List<Classify>) queryAll(Classify.class), v);
				sb.append(" and (goodsinfo.classId=?");
				ps.add(v);
				for (Classify c : list) {
					sb.append(" or goodsinfo.classId=?");
					ps.add(c.getClassId());
				}
				sb.append(")");
			}
		}
		sb.append(" order by goodsinfo.orderKey desc");
		// do count
		Query qCount = getSession().createQuery(hqlCount + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			qCount.setParameter(i, ps.get(i));
		}
		Long count = (Long) qCount.uniqueResult();
		page.setCount(count);
		// do result
		Query q = getSession().createQuery(hqlResult + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			q.setParameter(i, ps.get(i));
		}
		q.setFirstResult(page.getFirst());
		q.setMaxResults(page.getPageSize());
		page.setResult(q.list());
		return page;
	}

	/**
	 * 获取前端大类商品信息展示列表
	 * 
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<GoodsInfoExVO> getFrontList(Page<GoodsInfoExVO> page) {
		String hqlResult = "select new " + GoodsInfoExVO.class.getName() + GoodsInfoExVO.STRUCT;
		String hqlCount = "select count(goodsinfo)";
		StringBuffer sb = new StringBuffer();
		sb.append(" from ").append(GoodsInfo.class.getName()).append(" goodsinfo,");
		sb.append(Classify.class.getName()).append(" classify,");
		sb.append(AgeType.class.getName()).append(" agetype");
		sb.append(" where goodsinfo.ageId=agetype.ageId and goodsinfo.classId=classify.classId");
		sb.append(" and goodsinfo.status=2");

		List<Object> ps = new ArrayList<Object>();
		if (page.getParas().containsKey("classId")) {
			String classId = page.getParas().get("classId")[0];
			List<Classify> list = getSonClass((List<Classify>) queryAll(Classify.class), classId);
			sb.append(" and (goodsinfo.classId=?");
			ps.add(classId);
			for (Classify c : list) {
				sb.append(" or goodsinfo.classId=?");
				ps.add(c.getClassId());
			}
			sb.append(")");
		}
		if (page.getParas().containsKey("key")) {
			String key = page.getParas().get("key")[0];			
			sb.append(" and (goodsinfo.goodsName like ? or goodsinfo.recommend like ? )");
			ps.add("%" + key + "%");
			ps.add("%" + key + "%");
		}
		if (page.getParas().containsKey("ageId")) {
			String ageId = page.getParas().get("ageId")[0];			
			sb.append(" and goodsinfo.ageId=?");
			ps.add(ageId);
		}
		String order = "orderKey";
		if (page.getParas().containsKey("order")) {
			String n = page.getParas().get("order")[0];
			if("1".equals(n)){
				order="countLook";
			}else if("2".equals(n)){
				order="countComment";
			}
		} else {
			sb.append(" order by goodsinfo.orderKey desc");
		}
		sb.append(" order by goodsinfo." + order + " desc");
		// do count
		Query qCount = getSession().createQuery(hqlCount + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			qCount.setParameter(i, ps.get(i));
		}
		Long count = (Long) qCount.uniqueResult();
		page.setCount(count);

		// do result
		Query q = getSession().createQuery(hqlResult + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			q.setParameter(i, ps.get(i));
		}
		q.setFirstResult(page.getFirst());
		q.setMaxResults(page.getPageSize());
		page.setResult(q.list());
		return page;
	}

	/**
	 * 获取前端大类商品信息展示列表
	 * 
	 * @param classId
	 *            大类类型
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<GoodsInfoExVO> getFrontList(String classId) {
		String hqlResult = "select new " + GoodsInfoExVO.class.getName() + GoodsInfoExVO.STRUCT;
		StringBuffer sb = new StringBuffer();
		sb.append(" from ").append(GoodsInfo.class.getName()).append(" goodsinfo,");
		sb.append(Classify.class.getName()).append(" classify,");
		sb.append(AgeType.class.getName()).append(" agetype");
		sb.append(" where goodsinfo.ageId=agetype.ageId and goodsinfo.classId=classify.classId");
		sb.append(" and goodsinfo.status=2");

		List<Object> ps = new ArrayList<Object>();
		List<Classify> list = getSonClass((List<Classify>) queryAll(Classify.class), classId);
		sb.append(" and (goodsinfo.classId=?");
		ps.add(classId);
		for (Classify c : list) {
			sb.append(" or goodsinfo.classId=?");
			ps.add(c.getClassId());
		}
		sb.append(")");

		sb.append(" order by goodsinfo.createTime desc");

		// do result
		Query q = getSession().createQuery(hqlResult + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			q.setParameter(i, ps.get(i));
		}
		return (List<GoodsInfoExVO>) q.list();
	}

	/**
	 * 获取某节点的子节点
	 * 
	 * @param sou
	 * @param id
	 * @return
	 */
	private static List<Classify> getSonClass(List<Classify> sou, String id) {
		List<Classify> result = new ArrayList<Classify>();
		for (Classify g : sou) {
			if (id.equals(g.getPid())) {
				result.add(g);
				List<Classify> son = getSonClass(sou, g.getClassId());
				if (son.size() > 0) {
					result.addAll(son);
				}
			}
		}
		return result;
	}

	/**
	 * 根据ID获取商品信息对象信息
	 * 
	 * @param id
	 *            ID
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public GoodsInfoExVO getGoodsInfoVO(String goodsId) {
		GoodsInfoExVO result = null;
		StringBuffer sb = new StringBuffer();
		sb.append("select new ").append(GoodsInfoExVO.class.getName()).append(GoodsInfoExVO.STRUCT);
		sb.append(" from ").append(GoodsInfo.class.getName()).append(" goodsinfo,");
		sb.append(Classify.class.getName()).append(" classify,");
		sb.append(AgeType.class.getName()).append(" agetype");
		sb.append(" where goodsinfo.ageId=agetype.ageId and goodsinfo.classId=classify.classId and goodsinfo.goodsId=?");
		Query q = this.getSession().createQuery(sb.toString());
		q.setParameter(0, goodsId);
		List<GoodsInfoExVO> tmpList = (List<GoodsInfoExVO>) q.list();
		if (tmpList.size() > 0) {
			result = tmpList.get(0);
		}
		return result;
	}

	/**
	 * 删除商品信息
	 * 
	 * @param ids
	 *            多个ID
	 * @param user
	 *            当前用户
	 * @return
	 */
	public void remove(String[] ids) {
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(GoodsInfo.class.getName()).append(" goodsinfo where");
		for (int i = 0; i < ids.length; i++) {
			sb.append(0 == i ? "" : " or");
			sb.append(" goodsinfo.goodsId=?");
		}
		Query q = getSession().createQuery(sb.toString());
		for (int i = 0; i < ids.length; i++) {
			q.setParameter(i, ids[i]);
		}
		q.executeUpdate();
	}

	/**
	 * 更改商品信息状态
	 * 
	 * @param ids
	 *            多个ID
	 * @param status
	 *            状态
	 * @param user
	 *            当前用户
	 * @return
	 */
	public void updateStatus(String[] ids, int status, String user) {
		List<Object> ps = new ArrayList<Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(GoodsInfo.class.getName()).append(" goodsinfo set status=?");
		ps.add(status);
		// 0-编辑 1-待上架 2-审核并上架 3-下架 4-驳回
		switch (status) {
		case 2:
		case 4:
			sb.append(",checkor=?,checkTime=?");
			ps.add(user);
			ps.add(new Date());
			break;
		case 3:
			sb.append(",downer=?,downTime=?");
			ps.add(user);
			ps.add(new Date());
			break;
		}
		sb.append(" where ");
		for (int i = 0; i < ids.length; i++) {
			sb.append(0 == i ? "" : " or");
			sb.append(" goodsinfo.goodsId=?");
			ps.add(ids[i]);
		}
		Query q = getSession().createQuery(sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			q.setParameter(i, ps.get(i));
		}
		q.executeUpdate();
	}

	/**
	 * 获取最新上线物品数
	 * 
	 * @param classId
	 *            物品大分类
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int getNewGoodsCount(String classId) {
		String hqlCount = "select count(goodsinfo)";
		StringBuffer sb = new StringBuffer();
		sb.append(" from ").append(GoodsInfo.class.getName()).append(" goodsinfo");
		sb.append(" where goodsinfo.status=2");

		List<Object> ps = new ArrayList<Object>();
		List<Classify> list = getSonClass((List<Classify>) queryAll(Classify.class), classId);
		sb.append(" and (goodsinfo.classId=?");
		ps.add(classId);
		for (Classify c : list) {
			sb.append(" or goodsinfo.classId=?");
			ps.add(c.getClassId());
		}
		sb.append(")");
		sb.append(" and DATE_FORMAT(goodsinfo.createTime,'%Y-%m-%d')>=?");
		ps.add(getWeekTime());

		// do count
		Query qCount = getSession().createQuery(hqlCount + sb.toString());
		for (int i = 0; i < ps.size(); i++) {
			qCount.setParameter(i, ps.get(i));
		}
		Long count = (Long) qCount.uniqueResult();
		return count.intValue();
	}

	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 获取从当前时间往前推7天的日期
	 * 
	 * @return
	 */
	private static String getWeekTime() {
		Calendar c = Calendar.getInstance();
		c.add(Calendar.DATE, -7);
		return sdf.format(c.getTime());
	}	

}
